package db;

import entities.*;

import java.sql.*;
import java.util.*;

//Class that handles all db requests regarding users
public class DBUserManager {
	static Connection con = null;
	static PreparedStatement addUser;
	static PreparedStatement fetchUser;
	static PreparedStatement fetchUserById;
	static PreparedStatement updateUser;
	static PreparedStatement searchName;
	static PreparedStatement searchNameAndEA;

	private DBUserManager() {

	}

	private static void getConnection() throws SQLException {
		if (!Connector.isValidConnection(con)) {
			con = Connector.getConnection();
			addUser = con
					.prepareStatement("INSERT INTO tblUser  ( userType, firstName,"
							+ "lastName , emailAddress, phoneNumber,password )  VALUES (?,?,?,?,?,?)");

			fetchUser = con
					.prepareStatement("Select * from tblUser  where emailAddress=? and password=?");

			fetchUserById = con
					.prepareStatement("Select * from tblUser  where userID=?");

			updateUser = con
					.prepareStatement("Update  tblUser SET userType=? , firstName=? , lastName=? , emailAddress=? , phoneNumber=? , password=? "
							+ "where userID=?");

			searchName = con
					.prepareStatement("select * from tbluser d where NOT EXISTS (select initID from tblantifriends e where "
							+ "e.targID=? and e.initID=d.userId) and  NOT EXISTS (select targID from tblantifriends e where "
							+ "e.initID=? and e.targID=d.userId) and NOT EXISTS (select initiatorID from tblfriends c where "
							+ "c.targetID=? and c.initiatorID=d.userId) and NOT EXISTS "
							+ "(select targetID from tblfriends c where c.initiatorID=? and c.targetID=d.userId "
							+ ") and  firstName like ? and lastName like ? and userID <> ?");
			searchNameAndEA = con
					.prepareStatement("select * from tbluser d where NOT EXISTS (select initID from tblantifriends e where "
							+ "e.targID=? and e.initID=d.userId) and  NOT EXISTS (select targID from tblantifriends e where "
							+ "e.initID=? and e.targID=d.userId) and NOT EXISTS (select initiatorID from tblfriends c where "
							+ "c.targetID=? and c.initiatorID=d.userId ) and NOT EXISTS (select "
							+ "targetID from tblfriends c where c.initiatorID=? and c.targetID=d.userId) and "
							+ "(emailAddress like ? or lastName like ? or firstName like ?) "
							+ "and userID <> ?");

			if (!Connector.isValidConnection(con)) {
				throw new DBError("Cannot Create DB Connection");

			}
		}

	}

	public static ArrayList<User> searchUser(String sTerm1, String sTerm2, int userID) {
		ArrayList<User> users = new ArrayList<User>();
		if (sTerm1 == null) {
			return null;
		}

		try {
			getConnection();

			if (sTerm2 == null) {
				searchNameAndEA.clearParameters();

				searchNameAndEA.setInt(1, userID);
				searchNameAndEA.setInt(2, userID);
				searchNameAndEA.setInt(3, userID);
				searchNameAndEA.setInt(4, userID);
				searchNameAndEA.setString(5, "%" + sTerm1 + "%");
				searchNameAndEA.setString(6, "%" + sTerm1 + "%");
				searchNameAndEA.setString(7, "%" + sTerm1 + "%");
				searchNameAndEA.setInt(8, userID);
				System.out.println(searchNameAndEA);
				ResultSet rs = searchNameAndEA.executeQuery();
				while (rs.next()) {
					User user = new User();

					user.setUserType(rs.getString("userType"));
					user.setFirstName(rs.getString("firstName"));
					user.setLastName(rs.getString("lastName"));
					user.setUserName(rs.getString("emailAddress"));
					user.setPhoneNumber(rs.getString("phoneNumber"));
					user.setPassword(rs.getString("password"));
					user.setUserId(rs.getInt("userId"));
					System.out.println(user.getFirstName());
					users.add(user);
				}
				return users;

			} else {
				searchName.clearParameters();

				searchName.setInt(1, userID);
				searchName.setInt(2, userID);
				searchName.setInt(3, userID);
				searchName.setInt(4, userID);
				searchName.setString(5, "%" + sTerm1 + "%");
				searchName.setString(6, "%" + sTerm2 + "%");
				searchName.setInt(7, userID);
				ResultSet rs = searchName.executeQuery();
				while (rs.next()) {
					User user = new User();

					user.setUserType(rs.getString("userType"));
					user.setFirstName(rs.getString("firstName"));
					user.setLastName(rs.getString("lastName"));
					user.setUserName(rs.getString("emailAddress"));
					user.setPhoneNumber(rs.getString("phoneNumber"));
					user.setPassword(rs.getString("password"));
					user.setUserId(rs.getInt("userId"));
					System.out.println(user.getFirstName());
					users.add(user);
				}
				return users;
			}
		} catch (SQLException e) {
			System.out.println(e);
			return null;
		}

	}

	// creates a new user and returns the id of the newly created user
	public static int addNewUser(User user) {
		try {
			// setup statement
			getConnection();
			// if(addUser==null || addUser.isClosed()){
			// addUser = con
			// .prepareStatement("INSERT INTO tblUser  ( userType, firstName,"
			// +
			// "lastName , emailAddress, phoneNumber,password )  VALUES (?,?,?,?,?,?)");
			//
			// }
			addUser.clearParameters();

			// set values

			addUser.setString(1, user.getUserType());
			addUser.setString(2, user.getFirstName());
			addUser.setString(3, user.getLastName());
			addUser.setString(4, user.getUserName());
			addUser.setString(5, user.getPhoneNumber());
			addUser.setString(6, user.getPassword());
			int returnVal = addUser.executeUpdate();
			if (returnVal == 0)
				return -1; // error
			User newUser = lookupUser(user.getUserName(), user.getPassword());
			if (newUser == null)
				return -1; // error
			return newUser.getUserId();

		} catch (SQLException e) {
			throw new DBError(e);

		}

	}

	// pass in the username(email) and password to lookup any user
	public static User lookupUser(String email, String passwd) {
		// setup statement

		try {
			getConnection();
			// if(fetchUser==null){
			// fetchUser = con
			// .prepareStatement("Select * from tblUser  where emailAddress=?");
			//
			// }
			fetchUser.clearParameters();

			fetchUser.setString(1, email);
			fetchUser.setString(2, passwd);
			ResultSet rs = fetchUser.executeQuery();
			while (rs.next()) {
				User user = new User();

				user.setUserType(rs.getString("userType"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setUserName(email);
				user.setPhoneNumber(rs.getString("phoneNumber"));
				user.setPassword(rs.getString("password"));
				user.setUserId(rs.getInt("userId"));
				return user;
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return null;
		}
	}

	// pass in the userId to lookup any user
	public static User lookupUser(Integer id) {
		// setup statement

		try {
			getConnection();
			// if(fetchUser==null){
			// fetchUser = con
			// .prepareStatement("Select * from tblUser  where emailAddress=?");
			//
			// }
			fetchUserById.clearParameters();

			fetchUserById.setInt(1, id.intValue());
			ResultSet rs = fetchUserById.executeQuery();
			while (rs.next()) {
				User user = new User();

				user.setUserType(rs.getString("userType"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setUserName(rs.getString("emailAddress"));
				user.setPhoneNumber(rs.getString("phoneNumber"));
				user.setPassword(rs.getString("password"));
				user.setUserId(rs.getInt("userId"));
				return user;
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			return null;
		}
	}

	public static boolean updateUser(User user) {

		try {
			// setup statement
			getConnection();

			updateUser.clearParameters();

			// set values

			updateUser.setString(1, user.getUserType());
			updateUser.setString(2, user.getFirstName());
			updateUser.setString(3, user.getLastName());
			updateUser.setString(4, user.getUserName());
			updateUser.setString(5, user.getPhoneNumber());
			updateUser.setString(6, user.getPassword());
			updateUser.setInt(7, user.getUserId());
			int returnVal = updateUser.executeUpdate();

			if (returnVal == 0)
				return false; // error

			return true;

		} catch (SQLException e) {
			throw new DBError(e);

		}
	}
	
	public static ArrayList<User> getAllUsers() { //for admin use
		ResultSet rs;
		ArrayList<User> results = new ArrayList<User>();
		try {
			getConnection();
			Statement stmt = con.createStatement();
			rs = stmt.executeQuery("SELECT * FROM tbluser order by userType, lastName");
			while ((rs != null) && (rs.next())) {
				User user = new User();
				user.setUserId(rs.getInt("userID"));
				user.setUserType(rs.getString("userType"));
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setUserName(rs.getString("emailAddress"));
				user.setPhoneNumber(rs.getString("phoneNumber"));
				results.add(user);
			}
		}
		catch (SQLException e) {
			System.out.print(e.getMessage());
			return null;
		}
		return results;
	}
}
